package com.szholly.plug.form.excel.core.exp;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.szholly.plug.form.excel.model.ColumnProperty;
import com.szholly.plug.form.excel.model.DictionaryEntity;
import com.szholly.plug.form.excel.model.TableEntity;

public class ExpBefore extends ExpDataPar implements IExpBefore {

	public ExpBefore(TableEntity tableEntity,String dsName) {
		super(tableEntity,dsName);
	}
	
	@SuppressWarnings("null")
	public List<Map<String, String>> getExpDataByCondition(String strExpCondition) {
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		List<Map<String, String>> expData = null;
		try {
//			conn = ConnectionHelper.getCurrentContributionConnection(dsName);
			StringBuilder sbColName = new StringBuilder();
			StringBuilder sbJoinDicTable = new StringBuilder();
			StringBuilder sbJoinDicTableCond = new StringBuilder();
			List<ColumnProperty>  listColumn = tableEntity.getColumnPropertys();
			ColumnProperty colProperty = null;
			DictionaryEntity dicEntity = null;
			for (int i = 0; i < colLength; i++) {
				colProperty = listColumn.get(i);
				if (i < colLength - 1) {
					if(colProperty.getDictEntity() != null){
						dicEntity = colProperty.getDictEntity();
						String strTableName = dicEntity.getName();
						String strDicId = dicEntity.getDictId();
						String strDicName = dicEntity.getDictName();
						sbColName.append("a" + i + ".").append(strDicName).append(" ").append(colProperty.getColumnName()).append(",");
						sbJoinDicTable.append(" LEFT JOIN ").append(strTableName).append(" a" + i)
						.append(" ON ").append(" a" + i + ".").append(strDicId).append(" = ").append("a.").append(colProperty.getColumnName());
					}else{
						sbColName.append(" a." + colProperty.getColumnName() + ",");
					}
				}
				if (i == colLength - 1) {
					if(colProperty.getDictEntity() != null){
						dicEntity = colProperty.getDictEntity();
						String strTableName = dicEntity.getName();
						String strDicId = dicEntity.getDictId();
						String strDicName = dicEntity.getDictName();
						sbColName.append("a" + i + ".").append(strDicName).append(" ").append(colProperty.getColumnName());
						sbJoinDicTable.append(" LEFT JOIN ").append(strTableName).append(" a" + i)
						.append(" ON ").append(" a" + i + ".").append(strDicId).append(" = ").append("a.").append(colProperty.getColumnName());
					}
					else{
						sbColName.append(" a." + colProperty.getColumnName());
					}
				}
			}
			StringBuilder sbSqlSelData = new StringBuilder(" select ")
			.append(sbColName)
			.append(" from ").append(tableName).append(" a ")
			.append(sbJoinDicTable.toString())
			.append(" WHERE 1=1 ")
			.append(strExpCondition)
			.append(sbJoinDicTableCond.toString());
			System.out.println(sbSqlSelData.toString());
			stmt =  conn.createStatement();
			rs = stmt.executeQuery(sbSqlSelData.toString());
			expData = new ArrayList<Map<String,String>>();
			Map<String,String> mapData = null;
			while(rs.next()){
				mapData = new HashMap<String, String>();
				for (String colName: arrColumn) {
					mapData.put(colName, rs.getString(colName));
				}
				expData.add(mapData);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
//			DBUtil.close(conn);
//			DBUtil.close(stmt);
		}
		return expData;
	}

	@SuppressWarnings("null")
	public List<Map<String, String>> getExpDataBySQL(String strExpSQL) {
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		List<Map<String, String>> expData = null;
		try {
//			conn = ConnectionHelper.getCurrentContributionConnection(dsName);
			stmt =  conn.createStatement();
			rs = stmt.executeQuery(strExpSQL);
			expData = new ArrayList<Map<String,String>>();
			Map<String,String> mapData = null;
			while(rs.next()){
				mapData = new HashMap<String, String>();
				for (String colName: arrColumn) {
					mapData.put(colName, rs.getString(colName));
				}
				expData.add(mapData);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
//			DBUtil.close(conn);
//			DBUtil.close(stmt);
		}
		return expData;
	}

}
